#!/usr/bin/python3
# -*- coding:utf-8 -*-
#python version: 3.6.7

#requests访问网页用的
#time取时间用的
#json解析网站返回数据
#os用来取目录
import time
import json
import os
import sys
from openpyxl import Workbook
from openpyxl import load_workbook
#首先获取到脚本所在目录，并把工作目录设为脚本所在目录
cdw = os.path.realpath(__file__)
cdw = os.path.dirname(cdw)
#argv用来获取是否有传入参数
from sys import argv
sys.path.append(sys.path[0][: sys.path[0].rindex('/')])
from librp.visit_website import *
from librp.file_analysis import *
import update_cookies
os.chdir(cdw)

def pawhf(pwstr):
    print(pwstr)
    if os.path.isdir('/var/www/html/zbdd/'):
        f_html = open('/var/www/html/zbdd/nla.html', 'w')
    else:
        f_html = open('index.html', 'w')
    t_html = '<html>\n'
    t_html += '\t<head>\n'
    t_html += '\t\t<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />\n'
    t_html += '\t\t<title>zbcc1203线上库存报告</title>\n'
    t_html += '\t</head>\n'
    t_html += '\t<body><p>'
    t_html += pwstr.replace('\n', '<br>\n').replace(' ', '&nbsp;')
    t_html += '</p><p><a href="./'
    t_html += time.strftime('%Y%m%d', time.localtime())
    t_html += '%E7%BA%BF%E4%B8%8A%E5%BA%93%E5%AD%98%E8%B7%9F%E8%B8%AA.xlsx">'
    t_html += '具体库存情况请见附表</a></p>'
    t_html += '\n\t</body>\n</html>'
    f_html.write(t_html)
    f_html.close()

def totalstock():
    #尝试读取多点的curl文件
    try:
        file_curl = open('../cookies/chrome_curl/curl_dmall.sh')
        text_curl_str = file_curl.read()
        file_curl.close()
    except FileNotFoundError:
        pawhf('错误！文件{}不存在！！！(0x0000004c)'.format(
            ck_filename[i]
        ))
        exit()

    #取出多点的门店编号
    in_s = text_curl_str.index('storeIds=') + 9
    in_e = text_curl_str.index('&', in_s)
    storeid = text_curl_str[in_s: in_e]

    post_param = {
        'shops': storeid,
        'publishStatus': '1',
        'tag': '0',
        'wareTag': '512'
    }

    pyres = json.loads(s = get_post_html(
        'https://ware-partner.dmall.com/wareshops/exportParam',
        curl2header_dict(text_curl_str),
        curl2cookie_dict(text_curl_str),
        post_param
    ))

    post_param = {'uuidStr': pyres['data']}
    pyres = get_post_html(
        'https://ware-partner.dmall.com/wareshops/exportWareShops',
        curl2header_dict(text_curl_str),
        curl2cookie_dict(text_curl_str),
        post_param,
        True
    )
    file_curl = open('totalstock.xlsx', 'wb')
    file_curl.write(pyres)
    file_curl.close()

    in_f = open('input_about.json')
    in_t = in_f.read()
    in_about = json.loads(in_t)
    in_f.close()
    #打开下载好的表格，并放入list中
    wb = load_workbook('totalstock.xlsx')
    ws = wb[wb.sheetnames[0]]
    total_list = []
    for r in range(2, ws.max_row + 1):
        row_list = []  # 每一行建立一个list
        for c in [
            in_about['downxls']['store'],
            in_about['downxls']['sku'],
            in_about['downxls']['code'],
            in_about['downxls']['name'],
            in_about['downxls']['upc'],
            in_about['downxls']['stock']
        ]:
            v = ws.cell(r, c).value
            row_list.append(v)
        row_list[5] = float(row_list[5])
        total_list.append(row_list)
    #读取分类用的json
    in_f = open('classification.json')
    in_t = in_f.read()
    in_ci = json.loads(in_t)
    in_f.close()
    in_f = open('department.json')
    in_t = in_f.read()
    in_dm = json.loads(in_t)
    a_am = [i.split(' ') for i in in_dm.keys()]
    a_am = {i: j for i,j in a_am}
    in_dm['未知分类'] = '9未知'
    in_f.close()
    #读取A类商品表
    wb = load_workbook(in_about['classa']['filename'])
    ws = wb[wb.sheetnames[0]]
    aclass = []
    for r in range(2, ws.max_row + 1):
        aclass.append(str(ws.cell(r, in_about['classa']['codecolumn']).value))
        ci_key = ws.cell(r, in_about['classa']['classcolumn']).value[
            in_about['classa']['classkey'][0] : in_about['classa']['classkey'][1]
        ]
        in_ci[aclass[-1]] = ci_key + ' ' + a_am[ci_key]
    for tl in total_list:
        tl[1] = in_ci.get(tl[2], '未知分类')
        tl[0] = in_dm[tl[1]]
    total_list = sorted(total_list, key=(lambda x: [x[0], x[1], x[2], x[4]]))
    negative_ts = []
    low_ts = []
    low_a_ts = []
    negative_ts_d = {}
    low_ts_d = {}
    low_a_ts_d = {}
    for tl in total_list:
        if tl[5] <  0:
            negative_ts.append(tl)
            negative_ts_d.setdefault(tl[0], 0)
            negative_ts_d[tl[0]] += 1
        if tl[5] >= in_about['low'][0] and tl[5] <= in_about['low'][1]:
            low_ts.append(tl)
            low_ts_d.setdefault(tl[0], 0)
            low_ts_d[tl[0]] += 1
            if tl[2] in aclass:
                low_a_ts.append(tl)
                low_a_ts_d.setdefault(tl[0], 0)
                low_a_ts_d[tl[0]] += 1
    #print(negative_ts)
    #print(low_a_ts)
    #print(low_ts)
    #print(len(negative_ts))
    #print(len(low_a_ts))
    #print(len(low_ts))
    #print(negative_ts_d)
    #print(low_a_ts_d)
    #print(low_ts_d)
    negative_ts.insert(0, [
        '部门',
        '管理类别',
        '商品编码',
        '线上商品名称',
        '国条',
        '库存'
    ])
    low_ts.insert(0, [
        '部门',
        '管理类别',
        '商品编码',
        '线上商品名称',
        '国条',
        '库存'
    ])
    low_a_ts.insert(0, [
        '部门',
        '管理类别',
        '商品编码',
        '线上商品名称',
        '国条',
        '库存'
    ])
    wb = Workbook()
    ws1 = wb.active
    ws1.title = '负库存'
    for nla in negative_ts:
        ws1.append(nla)
    ws2 = wb.create_sheet(
        'A类低库存(库存为' +
        str(in_about['low'][0]) + '-' +
        str(in_about['low'][1]) + ')'
    )
    for nla in low_a_ts:
        ws2.append(nla)
    ws3 = wb.create_sheet(
        '全品低库存(库存为' +
        str(in_about['low'][0]) + '-' +
        str(in_about['low'][1]) + ')'
    )
    for nla in low_ts:
        ws3.append(nla)
    columnwidth = [
        ['A', 5],
        ['B', 16],
        ['C', 10],
        ['D', 40],
        ['E', 21],
        ['F', 8],
    ]
    for i in columnwidth:
        ws1.column_dimensions[i[0]].width = i[1]
        ws2.column_dimensions[i[0]].width = i[1]
        ws3.column_dimensions[i[0]].width = i[1]
    nlastr = time.strftime('%Y年%m月%d日', time.localtime())
    nlastr += '线上商品库存追踪情况：\n'
    if len(negative_ts) > 1:
        nlastr += '    线上负库存商品共：{}品\n    其中：\n'.format(
            len(negative_ts) - 1
        )
        for i in negative_ts_d.keys():
            nlastr += '        {}：{}品\n'.format(
                i[1: ],
                negative_ts_d[i]
            )
    if len(low_a_ts) > 1:
        nlastr += '    线上A类低库存商品共：{}品\n    其中：\n'.format(
            len(low_a_ts) - 1
        )
        for i in low_a_ts_d.keys():
            nlastr += '        {}：{}品\n'.format(
                i[1: ],
                low_a_ts_d[i]
            )
    if len(low_ts) > 1:
        nlastr += '    线上低库存商品共：{}品\n    其中：\n'.format(
            len(low_ts) - 1
        )
        for i in low_ts_d.keys():
            nlastr += '        {}：{}品\n'.format(
                i[1: ],
                low_ts_d[i]
            )
    nlastr += '\n以上数据由多点来客系统对接，数据查询下载时间：\n'
    nlastr += time.strftime('%Y年%m月%d日%H:%M:%S', time.localtime())
    nlastr += '\n线上A类商品清单使用新业务事业部于'
    nlastr += in_about['classa']['update']
    nlastr += '所发布的版本'
    pawhf(nlastr)
    htmldir = '/var/www/html/zbdd/'
    if os.path.isdir(htmldir):
        for mflist in os.listdir(htmldir):
            if mflist[-11:] == '线上库存跟踪.xlsx':
                os.remove(htmldir + mflist)
        wb.save(
            '/var/www/html/zbdd/' +
            time.strftime('%Y%m%d', time.localtime()) +
            '线上库存跟踪.xlsx'
        )
    else:
        wb.save(
            time.strftime('%Y%m%d', time.localtime()) +
            '线上库存跟踪.xlsx'
        )
    return nlastr
if __name__ == "__main__":
    totalstock()
